#!/usr/bin/perl -w 
# filename: mconvert.pl
#  feature: convert maillog to MySQL
#   Auther: chifeng <chifeng@gmail.com>
#     Date: Fri Apr 27 12:49:56 CST 2007
#  Version: 0.2
#
#
use strict;
use File::Tail;
use Time::localtime;
use Time::Local;
use POSIX qw(strftime);
use DBI;

my $filename="/var/log/maillog";

my $dbuser = "manalyzer";
my $dbpassword = "manalyzer99";
my $dbname = "manalyzer";
my $host = "localhost";
my $dbh = DBI->connect("dbi:mysql:database=$dbname",$dbuser,$dbpassword)
    or die "Can not connect DB server!\n";
my %mq=();

my %months_map = (
    'Jan' => 1, 'Feb' => 2, 'Mar' => 3,
    'Apr' => 4, 'May' => 5, 'Jun' => 6,
    'Jul' => 7, 'Aug' => 8, 'Sep' => 9,
    'Oct' => 10, 'Nov' =>11, 'Dec' =>12,
    'jan' => 1, 'feb' => 2, 'mar' => 3,
    'apr' => 4, 'may' => 5, 'jun' => 6,
    'jul' => 7, 'aug' => 8, 'sep' => 9,
    'oct' => 10, 'nov' =>11, 'dec' =>12,
);

sub time2epoch {
    my $t = shift; # yyyy-mm-dd hh:mm:ss
    $t =~ /^(\d+)-(\d+)-(\d+)\s+(\d+):(\d+):(\d+)/;
    my ($y, $m, $d, $h, $mn, $s) = ($1, $2, $3, $4, $5, $6);

    # according to Time::Local manual, namely, months start at 0, and
    # years have 1900 subtracted from them.
    $y -= 1900;
    $m -= 1;
    return timelocal ($s, $mn, $h, $d, $m, $y);
}

sub epoch2time {
    my $epoch = shift;
    return strftime "%Y-%m-%d %H:%M:%S", localtime $epoch;
}

sub insert_record {
#insert_record $mq{$qid}{qid},$mq{$qid}{maildate},$mq{$qid}{mailfrom},$mq{$qid}{rcptto},$mq{$qid}{sent},$mq{$qid}{deferred},$mq{$qid}{bounced} ;
    my $iqid = $_[0];
    my $imaildate = $_[1];
    my $imailfrom = $_[2];
    my $ircptto = $_[3];
    my $isent = $_[4];
    my $ideferred = $_[5];
    my $ibounced = $_[6];
    #print $iqid,$imaildate,$imailfrom,$ircptto,$isent,$ideferred,$ibounced."\n";
    my $sql = "insert into maillog(qid,maildate,mailfrom,rcptto,sent,deferred,bounced) values ('$iqid','$imaildate','$imailfrom','$ircptto','$isent','$ideferred','$ibounced' )";
    $dbh->do($sql);
    if ($dbh->err) {
	return 0;
    } else {
        return 1;
    }
}
my $tm = localtime;
my $thisyear=$tm->year + 1900;
#my $fp = IO::File->new($filename);
#while(<$fp>) {
my $file = File::Tail->new(name=>$filename, tail=>-1);
while($_=$file->read) {
    # insert a record with (qid,maildate,mailfrom)
    if($_ !~ /NOQUEUE:/){
    if($_ =~ /from=</) {
       my @ll = split(/ /,$_);
       my $maildate = time2epoch("$thisyear"."-"."$months_map{$ll[0]}"."-"."$ll[1] ".$ll[2]);
       my $qid = substr($ll[5],0,-1);
       my $mailfrom = substr($ll[6],6,-2);
       if (!(exists $mq{$qid})) {
          $mq{$qid} = {qid => "$qid",maildate => $maildate ,mailfrom => "$mailfrom",rcptto => "undef",sent => 0 ,deferred => 0 ,bounced => 0 };
       }
    }
    # update sent,deferred,bounced
    if($_ =~ /to=</) {
        if($_ =~ /status=sent/) {
            my @lsent = split(/ /,$_);
            my $sentqid = substr($lsent[5],0,-1);
            my $rcptto = substr($lsent[6],4,-2);
            if (exists $mq{$sentqid}) {
	        $mq{$sentqid}{sent} = 1;
	        $mq{$sentqid}{rcptto} = $rcptto;
            } 
        }elsif($_ =~ /status=bounced/){
            my @lbounced = split(/ /,$_);
            my $bouncedqid = substr($lbounced[5],0,-1);
            my $bouncedrcptto = substr($lbounced[6],4,-2);
            if (exists $mq{$bouncedqid}) {
	        $mq{$bouncedqid}{bounced} = 1;
	        $mq{$bouncedqid}{rcptto} = $bouncedrcptto;
            }
        }elsif($_ =~ /status=deferred/){
            my @ddeferred = split(/ /,$_);
            my $deferredqid = substr($ddeferred[5],0,-1);
            my $deferredrcptto = substr($ddeferred[6],4,-2);
            if (exists $mq{$deferredqid}) {
	        $mq{$deferredqid}{deferred} = $mq{$deferredqid}{deferred} + 1;
	        $mq{$deferredqid}{deferredrcptto} = $deferredrcptto;
            }
        }
    }
    if( $_ =~ /removed$/ ) {
	my @removedqid = split(/ /, $_);
	my $reqid = substr($removedqid[5],0,-1);
	if ( exists $mq{$reqid} ){
	    insert_record $mq{$reqid}{qid},$mq{$reqid}{maildate},$mq{$reqid}{mailfrom},$mq{$reqid}{rcptto},$mq{$reqid}{sent},$mq{$reqid}{deferred},$mq{$reqid}{bounced} ;
	    #print "$reqid Deleted Success!\n" if(delete( $mq{$reqid} ));
	    delete( $mq{$reqid} );
	}
    }
    }
}
